# -*- coding: utf-8 -*-

class Model
  
  @@cn = 0
  
  def initialize(table)
    
    @table = table
    #open(host, user, pass, database)
    
  end
  
  def open(host, user, pass, database)
    
    #@db = DBConnect.new(host, user, pass, database)
    @@cn += 1
    #print "<div>#{@table}</p>"
    
  end
  
  def close()
    
    #@db.close()
    @@cn -= 1
    if @@cn == 0 then
      $db.close()
    end
    
  end
  
  def connection()
    return @@cn
  end
  
  def query(sql)
    return $db.query(sql)
  end
  
  def exec(sql)
    $db.exec(sql)
  end
  
  def get_fields()
    return $db.list_fields(@table)
  end
  
  def escape_string(str)
    begin
      estr = $db.escape_string(str)
    rescue
      estr = "error"
    end
    return estr
  end
  
  def get_max_id()
    
    sql = "SELECT MAX(id) AS id FROM `#{@table}`"
    rs = query(sql)
    
    return rs[0]["id"].to_i
    
  end
  
  def get_data()
    
    sql = "SELECT * FROM `#{@table}`"
    #print "#{sql}<br />"
    values = query(sql)
    return values
    
  end
  
  def get_data_with_order(order)
    
    sql = "SELECT * FROM `#{@table}` ORDER BY #{order}"
    
    values = query(sql)
    return values
    
  end
  
  def get_data_with_order_and_limit(order, limit)
    
    sql = "SELECT * FROM `#{@table}` ORDER BY #{order} LIMIT #{limit}"
    
    values = query(sql)
    return values
    
  end
  
  def get_data_by_status(status)
    
    sql = "SELECT * FROM `#{@table}` WHERE #{status}"
    rs = query(sql)
    
    if rs.length != 0 then
      values = rs
    else
      values = []
    end
    return values
    
  end
  
  def get_data_by_status_with_order(status, order)
    
    sql = "SELECT * FROM `#{@table}` WHERE #{status} ORDER BY #{order}"
    values = query(sql)
    return values
    
  end
  
  def get_data_by_status_with_order_and_limit(status, order, limit)
    
    sql = "SELECT * FROM `#{@table}` WHERE #{status} ORDER BY #{order} LIMIT #{limit}"
    values = query(sql)
    return values
    
  end
  
  def get_field_by_status(field, status)
    
    sql = "SELECT #{field} FROM `#{@table}` WHERE #{status}"
    rs = query(sql)
    
    if rs.length != 0 then
      val = rs[0][field]
    else
      val = false
    end
    
    return val
    
  end
  
  def get_data_by_word(field, word)
    
    word = escape_string(word.to_s)
    field = escape_string(field.to_s)
    sql = "SELECT * FROM #{@table} WHERE #{field} LIKE '%#{word}%'"
    #print sql
    
    values = query(sql)
    return values
    
  end
  
  def get_data_by_word_with_order(column, word, order)
    
    word = escape_string(word.to_s)
    sql = "SELECT * FROM #{@table} WHERE #{column} LIKE '%#{word}%' ORDER BY #{order}"
    #print sql
    
    values = query(sql)
    return values
    
  end
  
  def get_data_by_value(column, value)
    
    value = escape_string(value.to_s)
    sql = "SELECT * FROM #{@table} WHERE #{column} = '#{value}'"
    #print sql
    
    values = query(sql)
    return values
    
  end
  
  def get_data_by_value_with_order(column, value, order)
    
    value = escape_string(value.to_s)
    sql = "SELECT * FROM `#{@table}` WHERE #{column} = '#{value}' ORDER BY #{order}"
    #print sql
    
    values = query(sql)
    return values
    
  end
  
  def get_column(field)
    
    sql = "SELECT #{field} FROM #{@table}"
    #print sql
    
    rs = query(sql)
    values = []
    i = 0
    
    rs.each do |val|
      values[i] = val[field]
      i += 1
    end
    
    return values
    
  end
  
  def get_column_with_id(field)
    
    sql = "SELECT id, #{field} FROM #{@table}"
    #print sql
    
    rs = query(sql)
    values = []
    i = 0
    
    rs.each do |val|
      wk = {}
      wk["id"] = val["id"]
      wk[field] = val[field]
      #print wk.to_s
      values[i] = wk
      i += 1
    end
    
    return values
    
  end
  
  def get_column_with_id_and_order(field, order)
    
    sql = "SELECT id, #{field} FROM #{@table} ORDER BY #{order}"
    #print sql
    
    rs = query(sql)
    values = []
    i = 0
    
    rs.each do |val|
      wk = {}
      wk["id"] = val["id"]
      wk[field] = val[field]
      #print "<p>#{wk.to_s}</p>"
      values[i] = wk
      i += 1
    end
    
    return values
    
  end
  
  def get_column_with_id_and_status_and_order(field, status, order)
    
    sql = "SELECT id, #{field} FROM #{@table} WHERE #{status} ORDER BY #{order}"
    #print sql
    
    rs = query(sql)
    values = []
    i = 0
    
    rs.each do |val|
      wk = {}
      wk["id"] = val["id"]
      wk[field] = val[field]
      #print "<p>#{wk.to_s}</p>"
      values[i] = wk
      i += 1
    end
    
    return values
    
  end
  
  def get_column_by_id(id, column)
    return get_field_by_id(id, column)
  end
  
  def get_field_by_id(id, field)
    
    if id == "" then
      id = 0
    end
    
    sql = "SELECT #{field} FROM `#{@table}` WHERE id=#{id}"
    
    rs = query(sql)
    
    if rs.length != 0 then
      val = rs[0][field]
    else
      val = ""
    end
    
    return val
    
  end
  
  def get_data_by_id(id)
    
    if id == "" then
      id = 0
    end
    
    sql = "SELECT * FROM `#{@table}` WHERE id=#{id}"
    #print sql
    rs = query(sql)
    #print rs.num_rows()
    
    values = {}
    
    if rs.length!=0 then
      values = rs[0]
    else
      
      fields = get_fields()
      fields.each do |fd|
        if fd=="id" then
          val = "0"
        else
          val = ""
        end
        values[fd] = val
      end
    end
    
    return values
    
  end
  
  def count_by_field(field, value)
    
    sql = "SELECT COUNT(*) AS num FROM #{@table} WHERE #{field}='#{value}'"
    rs = query(sql)
    return rs[0]["num"]
    
  end
  
  def count_by_column(column, value)
    
    return count_by_field(column, value)
    
  end
  
  def count()
  
    sql = "SELECT COUNT(*) AS num FROM `#{@table}`"
    rs = query(sql)
    return rs[0]["num"]
    
  end
  
=begin
  def count_by_field(field, value)
    sql = "SELECT COUNT(*) AS num FROM #{@table} WHERE #{field}='#{value}'"
    rs = query(sql)
    return rs[0]["num"]
  end
=end
  
  def apply(values)
    
    if values.has_key?('id') then
      id = values['id'].to_s
    else
      id = 0
    end
    
    if 1 > id.to_i
      values["id"] = get_max_id() + 1
	  #print "#{values["id"]}<br>"
      sql = insert(values)
    else
      sql = update(values, id)
    end
    
    #print sql
    exec(sql)
    
    return values["id"]
    
  end
  
  def insert(values)
  
    sql = "INSERT INTO `#{@table}` "
    
    col = ""
    val = ""
    fields = get_fields()
    #debug_print rs.num_fields()
    
    fields.each do |fd|
      #debug_print fd.name + "<br />"
      if values.has_key?(fd) then
        if col != "" then
          col += ","
          val += ","
        end
        col += fd
        val += "'" + escape_string(values[fd].to_s) + "'"
      end
    end
    
    sql += "(#{col}) VALUES (#{val})"
    return sql
    
  end
  
  def update(values, id)
    
    sql = "UPDATE `#{@table}` SET "
    col = ""
    
    rs = get_fields()
    #debug_print "#{table}<br />"
    
    rs.each do |fd|
      #debug_print fd.name + "<br />"
      #print values[fd] + "<br />"
      if values.has_key?(fd) then
        if col != "" then
          col += ","
        end
        col += "#{fd}='" + escape_string(values[fd].to_s) + "'"
      end
    end
    
    sql += "#{col} WHERE id=#{id}"
    return sql
    
  end
  
  def delete(id)
    
    sql = "DELETE FROM `#{@table}` WHERE id=#{id}"
    #print sql
    exec(sql)
    
  end
  
end
